﻿CREATE PROCEDURE admin.s_ReCreateComputedColumns
@DontJustRunIt bit
AS
BEGIN TRAN

SELECT o.name as TableName, c.name as ColumnName, x.text as Definition
INTO #ComputedColumns FROM sysobjects o
INNER JOIN syscolumns c ON c.id=o.id
INNER JOIN syscomments x ON x.id=c.id AND x.number=c.colid
WHERE o.xtype='U'

DECLARE ComputedColumns CURSOR LOCAL FOR
SELECT * FROM #ComputedColumns

DECLARE @TableName sysname, @ColumnName sysname, @Definition
nvarchar(3000), @SQL nvarchar(4000)

OPEN ComputedColumns
WHILE 1=1 BEGIN
FETCH NEXT FROM ComputedColumns INTO @TableName, @ColumnName,
@Definition
IF @@FETCH_STATUS<>0 BREAK

SET @SQL='ALTER TABLE '+QUOTENAME(@TableName)
+' DROP COLUMN '+QUOTENAME(@ColumnName)
EXEC (@SQL)
IF @@ERROR<>0 BEGIN ROLLBACK RETURN END
END
CLOSE ComputedColumns

EXEC('
ALTER FUNCTION F(@x int)
RETURNS int WITH SCHEMABINDING
AS BEGIN
RETURN @x+2
END
')

OPEN ComputedColumns
WHILE 1=1 BEGIN
FETCH NEXT FROM ComputedColumns INTO @TableName, @ColumnName,
@Definition
IF @@FETCH_STATUS<>0 BREAK

SET @SQL='ALTER TABLE '+QUOTENAME(@TableName)
+' ADD '+QUOTENAME(@ColumnName)+' AS '+@Definition
EXEC (@SQL)
IF @@ERROR<>0 BEGIN ROLLBACK RETURN END
END
CLOSE ComputedColumns
DEALLOCATE ComputedColumns

DROP TABLE #ComputedColumns
COMMIT
